package com.lvtulife.common.web.util.search;

import com.lvtulife.common.web.component.onekeyBuild.FieldColumn;
import com.lvtulife.common.web.util.JdomPojoXmlUtil;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * 查询语句生成工具类
 */
public class SearchUtil implements Serializable {

    private static final Logger logger = LoggerFactory.getLogger(SearchUtil.class);

    private StringBuffer sql = new StringBuffer();
    private List<FieldColumn> feildMap = new ArrayList<>(0);
    private List<Order> orders = new ArrayList<>(0);
    private PageBean pageBean = null;
    private String tables = "";
    private String params = "";

    public SearchUtil(SearchParams sp, PageBean pageBean) {

        this.orders.addAll(sp.getOrders());
        this.pageBean = pageBean;


        // 取出Mybatis 实体类配置信息
        JdomPojoXmlUtil jdom = new JdomPojoXmlUtil(sp.getConfigName(), "ibatis");

        this.feildMap.addAll(jdom.getFieldColumnList());
        this.tables = jdom.getJoinSql(sp.getparam()); // 查询表名
        this.params = jdom.getSelectSql(sp.getparam());// 查询结果

        if (sp.getParams() != null) {
            Iterator it = sp.getParams().keySet().iterator();
            while (it.hasNext()) {
                String name = (String) it.next();
                Object obj = sp.getParams().get(name);
                if(obj==null){
                    continue;
                }
                String value = obj.toString();
                addFilter(name, value);
            }
        }
    }


    public SearchUtil(SearchParams sp) {

        this.orders.addAll(sp.getOrders());

        // 取出Mybatis 实体类配置信息
        JdomPojoXmlUtil jdom = new JdomPojoXmlUtil(sp.getConfigName(), "ibatis");

        this.feildMap.addAll(jdom.getFieldColumnList());
        this.tables = jdom.getJoinSql(sp.getparam()); // 查询表名
        this.params = jdom.getSelectSql(sp.getparam());// 查询结果

        if (sp.getParams() != null) {
            Iterator it = sp.getParams().keySet().iterator();
            while (it.hasNext()) {
                String name = (String) it.next();
                Object obj = sp.getParams().get(name);
                if(obj==null){
                    continue;
                }
                String value = obj.toString();
                addFilter(name, value);
            }
        }
    }


    public String getCountSql() {
        StringBuffer getCountSql = new StringBuffer("select count(*) from ").append(tables).append(" where 1=1 ").append(getWhereSql());// 总记录条数
        logger.info("CountSQL:{}", getCountSql);
        return getCountSql.toString();
    }


    public String getResultSql() {
        StringBuffer getResultSql = new StringBuffer("select ").append(params).append(" from ").append(tables).append(" where 1=1 ").append(getWhereSql()).append(getOrderSql()).append(getPageSql());// 结果集
        logger.info("ResultSQL:{}", getResultSql);
        return getResultSql.toString();
    }

    private String getPageSql() {
        String pageSql = "";
        if (pageBean != null && pageBean.getRowsPerPage() > 0) {
            pageSql = " LIMIT " + (pageBean.getPage() - 1) * pageBean.getRowsPerPage() + "," + pageBean.getRowsPerPage();
        }
        return pageSql;
    }

    private String getWhereSql() {
        return sql.toString();
    }

    private String getOrderSql() {
        if (orders == null || orders.isEmpty() || orders.size()==0) {
            return "";
        }

        StringBuffer orderSql = new StringBuffer(" order by ");
        for (int i = 0; i < orders.size(); i++) {

            Order order = orders.get(i);
            if (order == null) {
                continue;
            }

            String field = order.getParamName();
            if (field.indexOf(".") != -1) {
                field = field.split("[.]")[1];
            }


            String newName = "";
            for (FieldColumn fieldColumn : feildMap) {
                if (fieldColumn.getField().equals(field)) {
                    newName = fieldColumn.getTableAsName() + "." + fieldColumn.getColumn();
                    break;
                }
            }

            if (StringUtils.isBlank(newName)) {
                break;
            }

            if (i != 0) {
                orderSql.append(" , ");
            }
            orderSql.append(" ").append(newName);
            if (order.isAsc()) {
                orderSql.append(" asc ");
            } else {
                orderSql.append(" desc ");
            }
        }

        return orderSql.toString();
    }

    /**
     * 添加过滤
     * <p>
     * 举例，name传递：QUERY_t#id_S_EQ
     * <p>
     * 举例，value传递：0
     */
    private void addFilter(String name, String value) {
        if (StringUtils.isBlank(value) || !name.startsWith("QUERY_")) {
            return;
        }

        String[] filterParams = StringUtils.split(name, "_");

        String columnName = "";
        String columnType = "";
        String operator = "";

        //"QUERY_t1#traGift_egMarketPrice_I_EQ" or "QUERY_t1#egMarketPrice_I_EQ"
        if (filterParams.length == 4){
            columnName = filterParams[1].replaceAll("#", ".");// 要过滤的字段名称 t1.egMarketPrice
            columnType = filterParams[2];// 字段类型 I
            operator = filterParams[3];// SQL操作符 EQ
        }else if(filterParams.length == 5 ) {
            columnName = (filterParams[1]+"_"+filterParams[2]).replaceAll("#", ".");// 要过滤的字段名称   t1.traGift_egMarketPrice
            columnType = filterParams[3];// 字段类型 I
            operator = filterParams[4];// SQL操作符 EQ
        }else{
            return;
        }

        // t1.userName
        if (columnName.indexOf(".") != -1) {
            columnName = columnName.split("[.]")[1];
        }
        // 自动添加缩写的表名 如 t1.name、t2.title
        String newName = "";
        for (FieldColumn fieldColumn : feildMap) {
            if (fieldColumn.getField().equals(columnName)) {
                newName += fieldColumn.getTableAsName() + "." + fieldColumn.getColumn();
                break;
            }
        }

        if (StringUtils.isBlank(newName)) {
            return;
        }

        // 添加操作符合值
        sql.append(" and ").append(newName).append(getSqlOperator(operator)).append(getSqlObjValue(columnType, operator, value));// 拼SQL
    }

    /**
     * 转换SQL操作符
     *
     * @param operator
     * @return
     */
    private String getSqlOperator(String operator) {
        if (StringUtils.equalsIgnoreCase(operator, "EQ")) {
            return " = ";
        }
        if (StringUtils.equalsIgnoreCase(operator, "NE")) {
            return " != ";
        }
        if (StringUtils.equalsIgnoreCase(operator, "LT")) {
            return " < ";
        }
        if (StringUtils.equalsIgnoreCase(operator, "GT")) {
            return " > ";
        }
        if (StringUtils.equalsIgnoreCase(operator, "LE")) {
            return " <= ";
        }
        if (StringUtils.equalsIgnoreCase(operator, "GE")) {
            return " >= ";
        }
        if (StringUtils.equalsIgnoreCase(operator, "LK")
                || StringUtils.equalsIgnoreCase(operator, "RLK")
                || StringUtils.equalsIgnoreCase(operator, "LLK")) {
            return " like ";
        }
        return " ";
    }

    private Object getSqlObjValue(String columnType, String operator, String value) {
        if (StringUtils.equalsIgnoreCase(columnType, "S")) {
            if (StringUtils.equalsIgnoreCase(operator, "LK")) {
                value = "%" + value + "%";
            } else if (StringUtils.equalsIgnoreCase(operator, "RLK")) {
                value = value + "%";
            } else if (StringUtils.equalsIgnoreCase(operator, "LLK")) {
                value = "%" + value;
            }
            return "'" + value + "'";
        }
        if (StringUtils.equalsIgnoreCase(columnType, "L") || StringUtils.equalsIgnoreCase(columnType, "I") || StringUtils.equalsIgnoreCase(columnType, "D") || StringUtils.equalsIgnoreCase(columnType, "ST") || StringUtils.equalsIgnoreCase(columnType, "BD") || StringUtils.equalsIgnoreCase(columnType, "FT") || StringUtils.equalsIgnoreCase(columnType, "DB") || StringUtils.equalsIgnoreCase(columnType, "BL")) {
            return "'" + value + "'";
        }
        return null;
    }
}
